CREATE VIEW [dbo].[vContactRelatedCompany]
AS
SELECT prospect.ID AS 'CurrentId', prospect.ContactKey AS 'CurrentContactKey', prospect.CompanyId AS 'CurrentCompanyId', relProspect.ID,
       relProspect.CompanyId, relProspect.Company, relProspect.IsCompany, relProspect.ContactKey, relProspect.FullName, relProspect.FullAddress,
       relProspect.MemberType, relProspect.Status, relProspect.LastFirst, relProspect.Title, relProspect.Prefix, relProspect.Suffix, relProspect.Designation,
       relProspect.Informal, relProspect.WorkPhone, relProspect.HomePhone, relProspect.Fax, relProspect.TollFree, relProspect.Email, relProspect.City,
       relProspect.StateProvince, relProspect.Zip, relProspect.Country, relProspect.FunctionalTitle, relProspect.ContactRank
  FROM [dbo].[vNetContact] AS relProspect
       INNER JOIN [dbo].[vNetContact] AS prospect ON (prospect.CompanyId = relProspect.ID OR
                                                      prospect.CompanyId = relProspect.CompanyId) AND relProspect.ID <> prospect.ID
WHERE (prospect.CompanyId <> '')
UNION
SELECT prospect.ID AS 'CurrentId', prospect.ContactKey AS 'CurrentContactKey', prospect.CompanyId AS 'CurrentCompanyId', relProspect.ID,
       relProspect.CompanyId, relProspect.Company, relProspect.IsCompany, relProspect.ContactKey, relProspect.FullName, relProspect.FullAddress,
       relProspect.MemberType, relProspect.Status, relProspect.LastFirst, relProspect.Title, relProspect.Prefix, relProspect.Suffix, relProspect.Designation,
       relProspect.Informal, relProspect.WorkPhone, relProspect.HomePhone, relProspect.Fax, relProspect.TollFree, relProspect.Email, relProspect.City,
       relProspect.StateProvince, relProspect.Zip, relProspect.Country, relProspect.FunctionalTitle, relProspect.ContactRank
  FROM [dbo].[vNetContact] AS relProspect
       INNER JOIN [dbo].[vNetContact] AS prospect ON prospect.ID = relProspect.CompanyId AND relProspect.ID <> prospect.ID
WHERE (prospect.IsCompany = 1)

GO